import pymysql
import re
import xlrd
"""
mysql与网页的联接操作
"""
# 联接mysql
def create_user(name, password):
    """
    根据账号密码进行创建用户
    :param name:
    :param password:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from users where name = %s"
    cursor.execute(sql,[name])
    data = cursor.fetchall()
    if len(data) > 0:
        return False
    sql = "insert into users(name,password) values(%s,%s)"
    cursor.execute(sql, [name, password])

    conn.commit()
    cursor.close()
    conn.close()


def Chang_password(nm, z, m):
    """
    根据新密码，和原来的账号密码进行改变密码操作
    :param nm: 新密码
    :param z: 账号
    :param m: 旧密码
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "update users set password= %s where name =%s and password =%s"
    cursor.execute(sql, [nm, z, m])
    conn.commit()
    cursor.close()
    conn.close()


def show_users():
    """
    展示所有用户
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from users"
    cursor.execute(sql)
    data = cursor.fetchall()
    table = []
    table.append(list(data[0].keys()))
    for i in data:
        table.append(list(i.values()))
    cursor.close()
    conn.close()
    return table


def Query_users(z, m):
    """

    :param z: 账号
    :param m: 密码
    :return: 账号密码是否匹配
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from users"
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    for item in data:
        if item['name'] == z and item['password'] == m:
            return True
    return False


def delete_user(z):
    """
   根据账号删除用户
    :param z:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    sql = "delete from users where name=%s"
    cursor.execute(sql, [z])

    conn.commit()
    cursor.close()
    conn.close()


def show_general_sheet():
    """
    展示总表中所有内容
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from general_sheet"
    cursor.execute(sql)
    data = cursor.fetchall()
    table = []
    if len(data)>0:table.append(list(data[0].keys()))
    for i in data:
        i['Purchase_date'] = str(i['Purchase_date'])
        i['Scheduled_scrap_date'] = str(i['Scheduled_scrap_date'])
        table.append(list(i.values()))
    cursor.close()
    conn.close()
    return table
def show_sheet_by_thead_and_name(thead,name):
    """
    展示某一列属性搜索
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from general_sheet where "+thead+" =%s"
    cursor.execute(sql,[name])
    data = cursor.fetchall()
    table = []
    if len(data)>0:table.append(list(data[0].keys()))
    for i in data:
        i['Purchase_date'] = str(i['Purchase_date'])
        i['Scheduled_scrap_date'] = str(i['Scheduled_scrap_date'])
        table.append(list(i.values()))
    cursor.close()
    conn.close()
    return table
def show_sheet_by_thead_and_name_vague(thead,name):
    """
    展示某一列属性模糊搜索
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    name = "%"+name+"%"
    sql = "select * from general_sheet where "+thead+" like %s"
    cursor.execute(sql,[name])
    data = cursor.fetchall()
    table = []
    if len(data)>0:table.append(list(data[0].keys()))
    for i in data:
        i['Purchase_date'] = str(i['Purchase_date'])
        i['Scheduled_scrap_date'] = str(i['Scheduled_scrap_date'])
        table.append(list(i.values()))
    cursor.close()
    conn.close()
    return table


def create_general_sheet_item(type, brand, name="", user="", department="", Historical_user="", Condition_now="",
                              service_number="", IP_address="", Wired_MAC_address="", Wireless_MAC_address="",
                              Is_it_inventory="", Hard_disk="", Memory="", CPU="", Graphics_card="", monitor="",
                              Asset_Number="", Asset_Manager="", Upgrade_maintenance_record="", Price="",
                              Purchase_date='0000-0-0', Scheduled_scrap_date='0000-0-0', address="",
                              Classified_level="", remarks="",VID="",PID="",serial_number="",CD_drive="",operating_system=""):
    """
    创建新固定资产条目
    :param type:
    :param brand:
    :param name:
    :param user:
    :param department:
    :param Historical_user:
    :param Condition_now:
    :param service_number:
    :param IP_address:
    :param Wired_MAC_address:
    :param Wireless_MAC_address:
    :param Is_it_inventory:
    :param Hard_disk:
    :param Memory:
    :param CPU:
    :param Graphics_card:
    :param monitor:
    :param Asset_Number:
    :param Asset_Manager:
    :param Upgrade_maintenance_record:
    :param Price:
    :param Purchase_date:
    :param Scheduled_scrap_date:
    :param address:
    :param Classified_level:
    :param remarks:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    if len(type) == 0 or type == " ":
        type = "unknown"
    if len(brand) == 0 or brand == " ":
        brand = "unknown"
    if len(re.findall(r"\d{4}-\d{1,2}-\d{1,2}", Purchase_date)) == 0:
        Purchase_date = '0000-0-0'
    if len(re.findall(r"\d{4}-\d{1,2}-\d{1,2}", Scheduled_scrap_date)) == 0:
        Scheduled_scrap_date = '0000-0-0'
    sql = "insert into general_sheet(type,brand,name,user,department,Historical_user,Condition_now,service_number,IP_address,Wired_MAC_address,Wireless_MAC_address,Is_it_inventory,Hard_disk,Memory,CPU,Graphics_card,monitor,Asset_Number,Asset_Manager,Upgrade_maintenance_record,Price,Purchase_date,Scheduled_scrap_date,address,Classified_level,remarks,serial_number,VID,PID,CD_drive,operating_system) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cursor.execute(sql,
                   [type, brand, name, user, department, Historical_user, Condition_now, service_number, IP_address,
                    Wired_MAC_address, Wireless_MAC_address, Is_it_inventory, Hard_disk, Memory, CPU, Graphics_card,
                    monitor, Asset_Number, Asset_Manager, Upgrade_maintenance_record, Price, Purchase_date,
                    Scheduled_scrap_date, address, Classified_level, remarks,serial_number,VID,PID,CD_drive,operating_system])
    conn.commit()
    cursor.close()
    conn.close()


def change_general_sheet_item_by_Asset_Number(Asset_Number_C, type, brand, name="", user="", department="",
                                              Historical_user="", Condition_now="", service_number="", IP_address="",
                                              Wired_MAC_address="", Wireless_MAC_address="", Is_it_inventory="",
                                              Hard_disk="", Memory="", CPU="", Graphics_card="", monitor="",
                                              Asset_Number="", Asset_Manager="", Upgrade_maintenance_record="",
                                              Price="", Purchase_date='0000-0-0', Scheduled_scrap_date='0000-0-0',
                                              address="", Classified_level="", remarks="",VID="",PID="",serial_number="",CD_drive="",operating_system=""):
    """
    根据固定资产编号改变固定资产信息
    :param Asset_Number_C:
    :param type:
    :param brand:
    :param name:
    :param user:
    :param department:
    :param Historical_user:
    :param Condition_now:
    :param service_number:
    :param IP_address:
    :param Wired_MAC_address:
    :param Wireless_MAC_address:
    :param Is_it_inventory:
    :param Hard_disk:
    :param Memory:
    :param CPU:
    :param Graphics_card:
    :param monitor:
    :param Asset_Number:
    :param Asset_Manager:
    :param Upgrade_maintenance_record:
    :param Price:
    :param Purchase_date:
    :param Scheduled_scrap_date:
    :param address:
    :param Classified_level:
    :param remarks:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "update general_sheet set type=%s,brand=%s,name=%s,user=%s,department=%s,Historical_user=%s,Condition_now=%s,service_number=%s,IP_address=%s,Wired_MAC_address=%s,Wireless_MAC_address=%s,Is_it_inventory=%s,Hard_disk=%s,Memory=%s,CPU=%s,Graphics_card=%s,monitor=%s,Asset_Number=%s,Asset_Manager=%s,Upgrade_maintenance_record=%s,Price=%s,Purchase_date=%s,Scheduled_scrap_date=%s,address=%s,Classified_level=%s,remarks=%s ,serial_number=%s,VID=%s,PID=%s ,CD_drive=%s,operating_system=%s where Asset_Number =%s"
    cursor.execute(sql,
                   [type, brand, name, user, department, Historical_user, Condition_now, service_number, IP_address,
                    Wired_MAC_address, Wireless_MAC_address, Is_it_inventory, Hard_disk, Memory, CPU, Graphics_card,
                    monitor, Asset_Number, Asset_Manager, Upgrade_maintenance_record, Price, Purchase_date,
                    Scheduled_scrap_date, address, Classified_level, remarks,serial_number,VID,PID, CD_drive,operating_system,Asset_Number_C])
    conn.commit()
    cursor.close()
    conn.close()
def delete_item(Asset_Number):
    """
   根据固定资产编号删除固定资产
    :param z:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    sql = "delete from general_sheet where Asset_Number=%s"
    cursor.execute(sql, [Asset_Number])

    conn.commit()
    cursor.close()
    conn.close()

def select_and_count(name):
    """
     选某一列输出列的元素
    :param name:
    :return:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # sql = "select distinct "+name +" from general_sheet"
    sql = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gdzc' AND TABLE_NAME = 'general_sheet'"
    cursor.execute(sql)
    conn.commit()
    column = []
    data = cursor.fetchall()
    for i in data:
        column.append(i['COLUMN_NAME'])
    if name not in column:
        return "别想注入数据库哈哈哈哈"

    sql = "select " +name+ ", count(*) as count from general_sheet group by "+name+" order by count desc"
    cursor.execute(sql)
    conn.commit()
    data = cursor.fetchall()
    # for i in data:
    #     print(i)
    cursor.close()
    conn.close()
    return data

def find_item_by_Asset_Number(Asset_Number):
    """
     根据固定资产编号，返回信息
    :param Asset_Number:
    """
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset='utf8', db="gdzc")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from general_sheet where Asset_Number =%s"
    cursor.execute(sql,[Asset_Number])
    data = cursor.fetchall()
    table = []
    if len(data)>0:
        table.append(list(data[0].keys()))
        for i in data:
            i['Purchase_date'] = str(i['Purchase_date'])
            i['Scheduled_scrap_date'] = str(i['Scheduled_scrap_date'])
            table.append(list(i.values()))
        cursor.close()
        conn.close()
        return table





if __name__ == '__main__':
    # create_general_sheet_item("电脑","联想",Asset_Number='00001')
    # change_general_sheet_item_by_Asset_Number('00001', "电脑", "联想", "boxian_yang", Asset_Number='00001')
    # delete_item('编号')
    # print(select_and_count('brand'))
    # print(find_item_by_Asset_Number('YKUD01'))
    # Chang_password('1','1','1')
    # show_users()
    # print(create_user('2','2'))
    # show_sheet_by_thead_and_name('department', '资讯部')
    show_sheet_by_thead_and_name_vague("brand", "闪迪")
    # create_general_sheet_item('电脑','联想','E5300148',user="杨伯贤",CPU="Dual 3.50Ghz Interl Pentium(R)G4560")
    # show_general_sheet()
    # create_user('1','1')
    # Query_users('1','1')
